#统计上个月1日至今的数据。

#shell脚本 ⬇
------

#! /bin/bash

#上个月1日
Last_Month_DATE=$(date -d "$(date +%Y%m)01 last month" +%Y-%m-01)

#根据TD_DATE计算年季度月日
V_PARYEAR=`date --date="$Last_Month_DATE" +%Y`
V_PARMONTH=`date --date="$Last_Month_DATE" +%m`
V_PARDAY=`date --date="$Last_Month_DATE" +%d`

V_month_for_quarter=`date --date="$Last_Month_DATE" +%-m`
V_PARQUARTER=$(((${V_month_for_quarter}-1)/3+1))

#计算所需要的日期字符串
V_Month="${V_PARYEAR}"_"${V_PARMONTH}"
V_QUARTER="${V_PARYEAR}"_Q"${V_PARQUARTER}"
V_Year="${V_PARYEAR}"

${HIVE_HOME} -S -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=16;
--小时
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT
    clue_nums,
    origin_type_stat,
    for_new_user,
    hourinfo,
    dayinfo,
    monthinfo,
    '1' as time_type,
    concat(dwm.yearinfo,'-',dwm.monthinfo,'-',dwm.dayinfo,' ', dwm.hourinfo) as time_str,
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
where CONCAT_WS('-', dwm.yearinfo, dwm.monthinfo, dwm.dayinfo)>='${Last_Month_DATE}';

--天
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT
    sum(clue_nums) as clue_nums,
    origin_type_stat,
    for_new_user,
    '-1' as hourinfo,
    dayinfo,
    monthinfo,
    '2' as time_type,
    concat(dwm.yearinfo,'-',dwm.monthinfo,'-',dwm.dayinfo) as time_str,
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
where CONCAT_WS('-', dwm.yearinfo, dwm.monthinfo, dwm.dayinfo)>='${Last_Month_DATE}'
GROUP BY dwm.yearinfo, dwm.monthinfo, dwm.dayinfo, dwm.origin_type_stat, dwm.for_new_user;

--月
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT
    sum(clue_nums) as clue_nums,
    origin_type_stat,
    for_new_user,
    '-1' as hourinfo,
    '-1' asdayinfo,
    monthinfo,
    '4' as time_type,
    concat(dwm.yearinfo,'-',dwm.monthinfo) as time_str,
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
where CONCAT_WS('-', dwm.yearinfo, dwm.monthinfo)>='${V_Month}'
GROUP BY dwm.yearinfo, dwm.monthinfo, dwm.origin_type_stat, dwm.for_new_user;


--年
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT
    sum(clue_nums) as clue_nums,
    origin_type_stat,
    for_new_user,
    '-1' as hourinfo,
    '-1' as dayinfo,
    '-1' as monthinfo,
    '5' as time_type,
    dwm.yearinfo as time_str,
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
where dwm.yearinfo>='${V_Year}'
GROUP BY dwm.yearinfo, dwm.origin_type_stat, dwm.for_new_user;
"